<?php session_start(); ?>
<!DOCTYPE HTML>
<html>
<head>
	<title>Stewie's Market</title>
	<link rel="stylesheet" href="cs2102.css">
</head>

<body>

	<!-- Site navigation menu -->
	<?php include 'nav.php'; 

	function init_db()
	{
		// Open database connection
		$dbHost = "sid3.comp.nus.edu.sg";
		$dbHostPort="1521";
		$dbServiceName = "sid3.comp.nus.edu.sg";
		$usr = "A0103863";
		$pswd = "cs2102std";
		$dbConnStr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$dbHost.")(PORT=".$dbHostPort."))(CONNECT_DATA=(SERVICE_NAME=".$dbServiceName.")))";
		$dbConn = oci_connect($usr,$pswd,$dbConnStr);

		if(!$dbConn)
		{
			$err = oci_error();
			print('Could not establish a database connection: ' . $err['message']);
		}
		return $dbConn;
	}

	function create_sql_from_filter($matric)
	{
		$sql = "SELECT a.title, a.category, a.condition, a.description, a.years_of_use, a.request, a.price, s.name, to_char(a.time_created, 'DD MON HH24:MI'), to_char(a.time_created, 'Dy DD-Mon-YYYY HH24:MI:SS') as date_created FROM ad a, student s WHERE a.student=s.matric AND s.matric='$matric' ORDER BY a.time_created DESC";
		return $sql;
	}

	function fetch_results_for_query($db, $query)
	{
		$stmt = oci_parse($db,$query);
		if (!oci_execute($stmt)) {
			$err = oci_error($stmt);
			print('Query failed: '.$err['message']);
		}
		// Create result array with one entry per row
		$res = array();
		while ($next = oci_fetch_row($stmt))
			array_push($res, $next); // append to res array
		return $res;
	}

	function print_results($res)
	{   
		if (count($res) > 0){
			echo "<table width='100%'>";
			echo "<col width='50%'>";
			echo "<col width='14%'>";
			echo "<col width='14%'>";
			echo "<col width='11%'>";
			echo "<col width='11%'>";
			echo "<tr>";
			echo "<td> <b> Item </td> </u>";
			echo "<td> <b> Request </u> </td>";
			echo "<td> <b> Price </u> </td>";
			echo "<td> <b> Time Submitted </u> </td>";
			echo "</tr>";
			foreach ($res as $col) { // for every row
				echo "<tr>";
				echo "<td><a href='detail_page.php?title=".$col[0]."&category=".$col[1]."&condition=".$col[2]."&description=".$col[3]."&years_of_use=".$col[4]."&request=".$col[5]."&price=".$col[6]."&student=".$col[7]."&date=".$col[8]."'>".$col[0]."</a></td>";
				echo "<td>".$col[5]."</td>";
				echo "<td>".$col[6]."</td>";
				echo "<td>".$col[8]."</td>";
				echo "<td><a href='delete_page.php?date=".$col[9]."'>Delete ad</a></td>";
				echo "</tr>";
			}
			echo "</table>";
		}else{
			print "You have no ads. Add an ad to the left.";
		}
	}
	?>

	<div class="container">
		<!-- Main content -->
		<br /><h1>Your ads</h1>
		<?php
			$db = init_db();
			$sql = create_sql_from_filter($_SESSION['user']);
			$res = fetch_results_for_query($db, $sql);
			print_results($res);
			?>
	
		<!-- Sign and date the page, it's only polite! -->
		<address>Made by Project group 31 for CS2102, NUS 2013.</address>
	</div>

</body>
</html>